--------------------------------------------------------------------------------
-- @name: update and index
-- @author: dion cho
-- @note: test case for the overhead of the update statement on the index
--------------------------------------------------------------------------------

set pages 10000
set lines 120
set timing off
set trimspool on

col item format a30
col deltas format a50

@capture_on

drop table t1 purge;

create table t1(c1 number, c2 number);

create index t1_n1 on t1(c1);

create index t1_n2 on t1(c2);


-- case1: how the single update works on the index
insert into t1 values(1, 1);
commit;

col dba new_value dba
select regexp_replace(column_value, 
	'leaf: 0x[[:xdigit:]]+[[:space:]]+([[:digit:]]+)[[:space:]]+[[:print:]]+', 
	'\1') as dba
from table(tpack.index_tree_dump(user, 'T1_N1'))
where regexp_like(column_value, 'leaf: 0x[[:xdigit:]]+[[:space:]]+[[:digit:]]+');

col file_no new_value file_no
col block_no new_value block_no
select
	dbms_utility.data_block_address_file(&dba) as file_no,
	dbms_utility.data_block_address_block(&dba) as block_no
from dual;

select * 
from table(tpack.block_dump(&file_no, &block_no, 0))
where 
	(column_value like 'row#%' 
		or column_value like 'col%');	

		
update t1 set c1 = 2 where c1 = 1;
commit;

select * 
from table(tpack.block_dump(&file_no, &block_no, 0))
where column_value like 'row#%' or column_value like 'col%';


-- case2: batch update

-- with commit for each update
truncate table t1;

insert into t1 select level, level from dual connect by level <= 10000;
commit;

analyze index t1_n1 validate structure;
select blocks, lf_blks, del_lf_rows from index_stats;

begin
	for idx in 1 .. 10000 loop
		update /*+ index(t1) */ t1 set c1 = c1 + 1 where c2 = idx;
		commit;
	end loop;
end;
/

analyze index t1_n1 validate structure;
select blocks, lf_blks, del_lf_rows from index_stats;

-- with commit for the whole update
truncate table t1;

insert into t1 select level, level from dual connect by level <= 10000;
commit;

analyze index t1_n1 validate structure;
select blocks, lf_blks, del_lf_rows from index_stats;

begin
	for idx in 1 .. 10000 loop
		update /*+ index(t1) */ t1 set c1 = c1 + 1 where c2 = idx;
	end loop;
	commit;
end;
/

analyze index t1_n1 validate structure;
select blocks, lf_blks, del_lf_rows from index_stats;

-- batch update
truncate table t1;

insert into t1 select level, level from dual connect by level <= 10000;
commit;

analyze index t1_n1 validate structure;
select blocks, lf_blks, del_lf_rows from index_stats;

update t1 set c1 = c1 + 1;

analyze index t1_n1 validate structure;
select blocks, lf_blks, del_lf_rows from index_stats;


@capture_off
